﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using CommonLibrary.DB;

namespace SedionFormDesigner.CustControls.ComboBox
{
    public partial class FrmSQL : Form
    {
        private Panel pnlSqlPrefix;
        private Panel pnlSqlCondit;
        private Boolean boolWhere = false;
        private String sqlPrefix = String.Empty;
        private String sqlCondit = String.Empty;
        private String sqlLaster = String.Empty;
        private Int32 colsCount, coorX = 5, coorY = 5;
        private static FrmSQL frmInstance;

        public FrmSQL()
        {
            InitializeComponent();

            String sqlStr = "SELECT name FROM sysobjects WHERE type = 'U'";
            System.Collections.Generic.List<String> tablesName = DBExecuteUtil.queryDataList(sqlStr);

            this.AddTabControl();
            this.CboTableName.DataSource = tablesName;
        }

        public static FrmSQL CreateFrmInstace(String sql)
        {
            if (frmInstance == null || frmInstance.IsDisposed)
                frmInstance = new FrmSQL();
            else
                frmInstance.RtbSQL.Text = sql;
            return frmInstance;
        }

        private void BtnProduSQL_Click(object sender, EventArgs e)
        {
            this.ShowSql();
        }

        private void CkeSelectAll_CheckedChanged(object sender, EventArgs e)
        {
            foreach (Control item in this.pnlSqlPrefix.Controls)
            {
                CheckBox ckeTemp = item as CheckBox;
                if (ckeTemp != null)
                {
                    if (CkeSelectAll.Checked == true)
                        ckeTemp.Checked = true;
                    else
                        ckeTemp.Checked = false;
                }
            }
        }

        private void CboTableName_TextChanged(object sender, EventArgs e)
        {
            this.pnlSqlPrefix.Controls.Clear();
            this.RefreshCols(this.CboTableName.Text.Trim(), this.LblConstTableName.Visible);

            this.LblConstTableName.Text = this.CboTableName.Text;
        }

        private void tabSQL_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.LblConstTableName.Visible = !this.LblConstTableName.Visible;
            this.boolWhere = false;
            this.sqlPrefix = String.Empty;
            this.sqlCondit = String.Empty;

            if (this.tabSQL.SelectedTab.Controls[0].Controls.Count == 0)
                this.RefreshCols(this.CboTableName.Text, this.LblConstTableName.Visible);
        }

        /// <summary>
        ///  刷新列号
        /// </summary>
        /// <param name="tableName"></param>
        private void RefreshCols(String tableName, Boolean condition)
        {
            Int32 rowsCount;
            String sqlStr = "SELECT name FROM syscolumns where id =(SELECT max(id) FROM sysobjects where Xtype='U' AND name ='" + tableName + "')";
            this.DisposeColsInfo();
            System.Collections.Generic.List<String> columnsName = DBExecuteUtil.queryDataList(sqlStr);
            colsCount = columnsName.Count;
            if (colsCount % 4 == 0)
                rowsCount = colsCount / 4;
            else
                rowsCount = colsCount / 4 + 1;
            for (int i = 0; i < colsCount; i++)
            {
                if (!condition)
                {
                    CheckBox ckeColName = new CheckBox();
                    this.pnlSqlPrefix.Controls.Add(ckeColName);
                    ckeColName.Size = new Size(70, 24);
                    ckeColName.Name = "Cbo" + columnsName[i].ToString();
                    ckeColName.Text = columnsName[i].ToString();

                    if (i % 4 == 0 && i != 0)
                    {
                        coorX = 5;
                        coorY += ckeColName.Height + 5;
                    }

                    ckeColName.Location = new Point(coorX, coorY);

                    TextBox txtColTitle = new TextBox();
                    this.pnlSqlPrefix.Controls.Add(txtColTitle);

                    txtColTitle.Size = new Size(88, 24);
                    txtColTitle.Name = "Txt" + columnsName[i].ToString();
                    txtColTitle.Location = new Point(coorX + ckeColName.Width + 5, coorY);

                    coorX += ckeColName.Width + txtColTitle.Width + 15;
                }
                else
                {
                    if (columnsName[i].ToString() == "user_id")
                    {
                        Label lblColName = new Label();
                        this.pnlSqlCondit.Controls.Add(lblColName);

                        lblColName.Size = new Size(90, 24);
                        lblColName.Font = new Font("宋体", 12, FontStyle.Bold);
                        lblColName.TextAlign = ContentAlignment.MiddleCenter;
                        lblColName.BorderStyle = BorderStyle.FixedSingle;
                        lblColName.Name = "Lbl" + columnsName[i].ToString();
                        lblColName.Text = columnsName[i].ToString();

                        lblColName.DoubleClick += delegate
                        {
                            var colName = lblColName.Text;
                            sqlCondit = colName + "=@" + colName;
                            this.ShowSql();
                        };

                        if (i % 4 == 0 && i != 0)
                        {
                            coorX = 5;
                            coorY += lblColName.Height + 5;
                        }

                        lblColName.Location = new Point(coorX, coorY);
                        coorX += lblColName.Width + 100;
                    }
                }

            }
        }

        private void BtnConfirm_Click(object sender, EventArgs e)
        {
            this.DialogResult = DialogResult.OK;
        }

        private void BtnCancel_Click(object sender, EventArgs e)
        {
            this.DialogResult = DialogResult.Cancel;
        }

        public String GetSQL()
        {
            return this.RtbSQL.Text.Trim();
        }

        /// <summary>
        ///  在TabPage中添加Panel
        /// </summary>
        private void AddTabControl()
        {
            this.pnlSqlPrefix = new Panel();
            this.tabPage1.Controls.Add(pnlSqlPrefix);
            this.pnlSqlPrefix.Show();
            this.pnlSqlPrefix.Dock = DockStyle.Fill;
            this.RefreshCols(this.CboTableName.Text, this.LblConstTableName.Visible);

            this.pnlSqlCondit = new Panel();
            this.tabPage2.Controls.Add(pnlSqlCondit);
            this.pnlSqlCondit.Show();
            this.pnlSqlCondit.Dock = DockStyle.Fill;
        }

        // 生成SQL语句 仅查询半段
        private void GenerSqlPrefix()
        {
            String sqlStr = String.Empty;
            String sqlBody = String.Empty;
            foreach (Control item in this.pnlSqlPrefix.Controls)
            {
                CheckBox ckeTemp = item as CheckBox;
                if (ckeTemp != null && ckeTemp.Checked == true)
                {
                    sqlBody += ", " + item.Text;
                    TextBox txtTemp = this.pnlSqlPrefix.Controls[item.Name.Replace("Cbo", "Txt")] as TextBox;
                    if (txtTemp != null && !String.IsNullOrEmpty(txtTemp.Text.Trim()))
                        sqlBody += " as " + txtTemp.Text;
                }
            }

            if (!String.IsNullOrEmpty(sqlBody))
            {
                sqlBody = sqlBody.Substring(1);
                sqlPrefix = "select" + sqlBody + " from " + this.CboTableName.Text;
                // this.RtbSQL.Text = sqlStr;
            }
        }

        private void GernerSqlCondit()
        {
            if (!String.IsNullOrEmpty(sqlCondit))
            {
                if (!boolWhere)
                {
                    sqlLaster += " where " + sqlCondit;
                    boolWhere = true;
                }
                else
                    sqlLaster += " and " + sqlCondit;
            }
        }

        // 重置全局变量
        private void DisposeColsInfo()
        {
            colsCount = 0;
            coorX = 5;
            coorY = 5;
        }

        private void ShowSql()
        {
            this.GenerSqlPrefix();
            this.GernerSqlCondit();
            this.RtbSQL.Text = sqlPrefix + sqlLaster;

            //if (!String.IsNullOrEmpty(sqlPrefix) && !String.IsNullOrEmpty(sqlCondit))
            //{
            //    if (!boolWhere)
            //    {
            //        this.RtbSQL.Text += sqlPrefix + " where " + sqlCondit;
            //        boolWhere = true;
            //    }
            //    else
            //        this.RtbSQL.Text += " and " + sqlCondit;

            //    sqlCondit = String.Empty;
            //}
        }

        private void BtnClearAll_Click(object sender, EventArgs e)
        {
            this.sqlCondit = String.Empty;
            this.sqlLaster = String.Empty;
            this.sqlPrefix = String.Empty;
        }
    }
}
